GEO 3/330 Exercise #1

Exploration of Transportation Mode Share in the US using Census Journey to Work Data

Purpose

This exercise uses means of transportation to work data to evaluate division, state and county-level commuting trends of workers across the United States between 2010 and 2021. Students will use census geography and attribute data to create maps, tables and figures that support the exploration of work commuting behavior by means of transportation (e.g., drive alone, bicycle, walking). The R code that was used to create the tables and maps is provided for reference but you don’t need to use R to complete exercise.

Step 1. Download exercise template and data

Download the exercise PowerPoint template and journey to work data (as Excel spreadsheet) from GitHub using the provided links. You will use the Excel table to explore data and format tables and figures that will be shared and summarized in your PowerPoint presentation. You will also draw from resources on this page (e.g., maps) to complete your presentation. For now, just download the template and spreadsheet.

Journey to work data

For this exercise, students will examine trends in commute mode share across US divisions, states and counties over the past decade using a curated dataset. The data were downloaded using the censusapi package in RStudio. The package leverages the US Census Bureau’s application programming interface (API), which supports custom queries and downloads of ACS and other census data. Other R packages used to import data as well as create maps, tables and figures for this exercise include tigris, dplyr, tidyverse, sf, and leaflet.

Code

library(censusapi) # used to download census attribute data
library(tigris) # used to download census geographies/geometries
library(tidyverse) # used for data wrangling
library(data.table) # used for data wrangling
library(DT) # used for formatting tables
library(tidyr) # used for data wrangling
library(dplyr)  # used for data wrangling
library(openxlsx) # used for reading/writing from/to Excel
library(sf) # used for reading geography data
library(leaflet) # used for creating interactive maps
library(plotly)
library(ggplot2)
library(oceanis) # used for exporting maps to png
library(keyring)

The commute mode share data by US county were obtained from table B08301 of the American Community Survey 5-year estimates. These estimates represent data collected over a 5-year period of time (which increases the statistical reliability of the data).

The American Community Survey (ACS) is an ongoing survey that covers a broad range of topics including the social, economic, demographic, and housing characteristics of the US population. The US census also collects “journey to work” information that can be used for a variety of planning and policy purposes including those related to sustainable transportation.

The journey to work data represent responses to a specific question administered in the survey: “How did this person usually get to work last week?” If this person used more than one mode of transportation, they were asked to select the transportation mode used for most of the distance. For example, respondents would select “bicycle” or “walk” only if that mode represented most of the commute distance. In this way, the ACS only captures the primary commuting mode for the week before the survey, which may not represent the annual commuting mode share. Such limitations notwithstanding, the ACS represents the most reliable source of temporal changes in commute share data across the US.

Code

# Tables and years to download
grouplist <- c("B08301")
yearlist <- c(2010:2021)

# Download attribute data from ACS by county
for (agroup in grouplist) {
  for (ayear in yearlist) {
    agroupname = paste("group(",agroup,")",sep="")
    acs_group <- getCensus(name = "acs/acs5",
                           vintage = ayear,
                           vars = c("NAME", "B01001_001E",agroupname),
                           region = "county:*", # tracts
                           # regionin="*", # places, counties, not msas
                           key=key_get("census_api_key"))
    attach(acs_group)
    acs_group <- acs_group %>% select(-contains(c("EA", 
                                                  "MA", 
                                                  "GEO_ID", 
                                                  "M_1", 
                                                  "M")))
    acs_group$year<-ayear # append data with data year
    acs_group$GEOID_county<-paste0(state,county)
    assign(paste(agroup,"county",ayear,sep="_"),acs_group)
    rm(acs_group)
    detach(acs_group)
  }
}

apattern <- paste(agroup,"county",sep="_")
alist_dfs <- mget(ls(pattern = apattern))
modeshare_county_2000_2021 <- rbindlist(alist_dfs)

# Download census geographies using tigris
us_states_geom <- states(class="sf")
us_counties_geom <- counties(class="sf", cb=TRUE, resolution = "20m")
us_divisions_geom <- divisions(class="sf", resolution = "20m")

# reformat data, column names for ease of use
# transform mode share from counts to percentages

modeshare_county_2000_2021_formatted <- modeshare_county_2000_2021 %>%
  left_join(us_states_geom %>% 
              st_drop_geometry() %>%
              select(
                state_name = NAME,
                GEOID_state = STATEFP,
                GEOID_division = DIVISION),
            by = c("state" = "GEOID_state")) %>%
  left_join(us_divisions_geom %>% 
              st_drop_geometry() %>%
              select(
                division_name = NAME,
                GEOID_division = GEOID),
            by = "GEOID_division") %>%
  drop_na(division_name) %>%
  left_join(us_counties_geom %>% 
              st_drop_geometry() %>%
              select(
                county_name = NAME,
                GEOID_county = GEOID),
            by = "GEOID_county") %>%
  drop_na(county_name) %>%
rename(total_population = B01001_001E,
       workers16pl = B08301_001E,
       drovealone = B08301_003E,
       carpool = B08301_004E,
       transit = B08301_010E,
       taxi = B08301_016E,
       motorcycle = B08301_017E,
       bicycle = B08301_018E,
       walk = B08301_019E,
       fromhome = B08301_021E) %>%
  mutate(other = workers16pl - drovealone - carpool - transit - taxi - motorcycle - bicycle - walk - fromhome,
         pct_drovealone = drovealone/workers16pl*100,
         pct_carpool=carpool/workers16pl*100,
         pct_transit=transit/workers16pl*100,
         pct_taxi=taxi/workers16pl*100,
         pct_motorcycle=motorcycle/workers16pl*100,
         pct_bicycle=bicycle/workers16pl*100,
         pct_walk=walk/workers16pl*100,
         pct_fromhome=fromhome/workers16pl*100,
         pct_other=other/workers16pl*100) %>%
  select(GEOID_county,
         county_name,
         state_name,
         division_name,
         year,
         total_population,
         workers16pl,
         total_population,
         drovealone,
         carpool,
         transit,
         taxi,
         motorcycle,
         bicycle,
         walk,
         fromhome,
         pct_drovealone:pct_other)

Step 2. Evaluate trends in journey to work over time

Open the Exercise_01.xlsx Excel spreadsheet. Here you will find 4 worksheets. Open the mode share trends dashboard worksheet. This worksheet allows you to display commute mode share over time at the national level and for selected census divisions and/or states by transportation mode. Clicking one of the nine census divisions (e.g., East South Central) in the far left panel, will automatically select the associated states (Alabama, Kentucky, Mississippi and Tennessee) within the division and corresponding commute mode share trends and statistics (e.g., percent of workers 16 and older who commuted to work by driving alone). Custom selections (e.g., selecting walk and bicycle will return the combined “active transportation” mode share) can be made by using the “multi-select” tool on the upper right of each panel. Click the filter button on the upper right of each panel to clear selections and begin a new query.

What changes and trends do you see in mode share over this decade-long period nationally, within particular census divisions and/or states? How do these trends differ across these census geographies? Speculate as to what factors (e.g., global and/or local) may explain any differences and/or similarities between locations. Create a couple custom charts in the mode share trends dashboard that are of interest to you; that display mode share trends for a specific area of the country (i.e., state or division) and transportation mode over time. Copy each chart to your exercise PowerPoint presentation (slides 1 and 2) and summarize your insights in the space provided on each slide.

The figures below show (a) the interquartile range of each transportation mode across all counties (Figure 1); and (b) trends in commute mode share over time for select modes (Figure 2).

Code

plot_ly(modeshare_county_2000_2021_pivoted, 
        y = ~percent, 
        color = ~reorder(mode,order), 
        type = "box",
        jitter = 0.5,
        boxpoints = 'suspectedoutliers',
        quartilemethod="inclusive",
        marker = list(size = 0.75)) %>%
  config(displaylogo = FALSE,
         modeBarButtons = list(modeBarButtonsList)) %>%
  layout(yaxis = list(title = 'Commute Mode Share (%)'),
         xaxis = list(title = 'Travel Mode'),
         showlegend = FALSE)

Figure 1: Interquartile Box Plots of Commute Mode Share among US Counties, 2010-2021

Code

plot_ly(data = modeshare_county_2000_2021_formatted %>% 
          select(year,pct_fromhome, pct_bicycle, pct_transit, pct_walk) %>%
          drop_na() %>%
          group_by(year) %>%
          summarise(`from home` = mean(pct_fromhome),
                    `bicycle` = mean(pct_bicycle),
                    `walk` = mean(pct_walk),
                    `transit` = mean(pct_transit)),
        x = ~year,
        y = ~`from home`,
        type = 'scatter', 
        mode = 'lines+markers',
        marker = list(color = "#E0A100"),
        line = list(color = "#E0A100"),
        name = 'from home',
        hovertemplate = 'from home: %{y:.1f}<extra></extra>') %>%
    add_trace(x = ~year,
        y = ~`walk`,
        type = 'scatter', 
        mode = 'lines+markers',
        marker = list(color = "#9F1928"),
        line = list(color = "#9F1928"),
        name = 'walk',
        hovertemplate = 'walk: %{y:.1f}<extra></extra>') %>%
    add_trace(x = ~year,
        y = ~`transit`,
        type = 'scatter', 
        mode = 'lines+markers',
        marker = list(color = "#009BA6"),
        line = list(color = "#009BA6"),
        name = 'transit',
        hovertemplate = 'transit: %{y:.1f}<extra></extra>') %>%
    add_trace(x = ~year,
        y = ~`bicycle`,
        type = 'scatter', 
        mode = 'lines+markers',
        marker = list(color = "#080967"),
        line = list(color = "#080967"),
        name = 'bicycle',
        hovertemplate = 'bicycle: %{y:.1f}<extra></extra>') %>%
      layout(
      xaxis = list(title = ""), 
      yaxis = list(title = "Commute Mode Share (%)"),
      legend=list(
        font = list(
          size = 10
        ),
        orientation = "h", 
        xanchor="center", 
        x = 0.5, y=-0.1),
      hovermode = "x unified")

Figure 2: Average Commute Mode Share among US Counties by Select Modes, 2010-2021

Step 3. Create top ten lists by commute mode share performance among US counties

Now open the mode share 2019 data worksheet in the Excel spreadsheet. This worksheet includes mode share by county for 2019 exclusively (prior to the COVID-19 pandemic in the US). (Refer to the associated dictionary worksheet for descriptions of the variable names.) Use sorting tools to identify the top ten counties for a specific mode of transportation that is of interest to you. (You may also use a numeric filter on the “total_population” and/or “workers16pl” columns to target counties of a particular size.) Copy associated data for your top ten lists into the table shells provided in slides 3 and 4 of the PowerPoint slide deck and respond to the associated questions.

Joining mode share data to census geography data

The US Census makes both attribute and geographic data available for multiple geographic levels. Census geographies follow a standard hierarchy. For this exercise, we evaluate counties by state and division which can be downloaded in R using the tigris package. Once downloaded, the geographies can be joined with attribute data to make meaningful maps.

Code

modeshare_county_2019 <- modeshare_county_2000_2021_formatted %>% 
  filter(year==2019,
         state_name != "Alaska",
         state_name != "Hawaii") 

modeshare_county_2019_geom <- us_counties_geom %>%
  select(GEOID_county = GEOID) %>%
  left_join(modeshare_county_2019, 
            by="GEOID_county") %>%
  st_as_sf() %>%
  st_transform(4326) %>%
  drop_na()

Step 4. Add selected maps to presentation

Download links to static versions of the maps to copy and paste into PowerPoint template. Figure 3 presents interactive commute mode share maps by county across the US for select travel modes. Hover over each county to display its respective mode share, census division and state. Do you see any geographic patterns in these mode share distributions? Why do you think particular divisions and states perform differently/similarly? Identify two maps you’d like to include in your presentation (slides 6, 7). Save the maps to your computer using the “static map” links located by the figure’s caption. Summarize your insights in the presentation.

Code

# write_csv(modeshare_county_2019_geom %>% st_drop_geometry(), "data/Exercise_01.csv")

# plot state and county maps with leaflet
countylabels <- sprintf(
  "<strong>%s Division</strong><br/>
  <strong>%s</strong><br/>
  Walk: %0.1f%%<br/>
  Bicycle: %0.1f%%<br/>
  Transit: %0.1f%%<br/>
  Drove Alone: %0.1f%%<br/>
  Home: %0.1f%%",
  modeshare_county_2019_geom$division_name,
  modeshare_county_2019_geom$county_name,
  modeshare_county_2019_geom$pct_walk,
  modeshare_county_2019_geom$pct_bicycle,
  modeshare_county_2019_geom$pct_transit,
  modeshare_county_2019_geom$pct_drovealone,
  modeshare_county_2019_geom$pct_fromhome) %>%
lapply(htmltools::HTML)

# walk mode share
bins_walk <- c(0,1.273,2.184,3.5,40)
pal_walk <- colorBin("Blues", domain = modeshare_county_2019_geom$pct_walk, bins = bins_walk)

walk_map <- leaflet(modeshare_county_2019_geom) %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  addPolygons(
    fillColor = ~pal_walk(pct_walk),
    weight = 0.5,
    opacity = 1,
    color = "white",
    dashArray = "3",
    fillOpacity = 0.7,
    label = countylabels) %>%
  addLegend("bottomleft", pal = pal_walk, values = ~pct_walk,
    title = "Walk Mode Share",
    labFormat = labelFormat(suffix = "%"),
    opacity = 1
  )

# bicycle mode share
bins_bicycle <- c(0,0.1249,0.3486,0.5,8.7)
pal_bicycle <- colorBin("Reds", domain = modeshare_county_2019_geom$pct_bicycle, bins = bins_bicycle)

bicycle_map <- leaflet(modeshare_county_2019_geom) %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  addPolygons(
    fillColor = ~pal_bicycle(pct_bicycle),
    weight = 0.5,
    opacity = 1,
    color = "white",
    fillOpacity = 0.7,
    label = countylabels) %>%
  addLegend("bottomleft", pal = pal_bicycle, values = ~pct_bicycle,
    title = "Bicycle Mode Share",
    labFormat = labelFormat(suffix = "%"),
    opacity = 1
  )

# transit mode share
bins_transit <- c(0,0.8,0.30,0.72,62)
pal_transit <- colorBin("Greens", domain = modeshare_county_2019_geom$pct_transit, bins = bins_transit)

transit_map <- leaflet(modeshare_county_2019_geom) %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  addPolygons(
    fillColor = ~pal_transit(pct_transit),
    weight = 0.5,
    opacity = 1,
    color = "white",
    dashArray = "3",
    fillOpacity = 0.7,
    label = countylabels) %>%
  addLegend("bottomleft", pal = pal_transit, values = ~pct_transit,
    title = "Transit Mode Share",
    labFormat = labelFormat(suffix = "%"),
    opacity = 1
  )
 
# drive alone share
bins_drovealone <- c(0,77.73,81.25,84.08,98)
pal_drovealone <- colorBin("Greys", domain = modeshare_county_2019_geom$pct_drovealone, bins = bins_drovealone)


drove_map <- leaflet(modeshare_county_2019_geom) %>%
  addProviderTiles(providers$CartoDB.Positron) %>%
  addPolygons(
    fillColor = ~pal_drovealone(pct_drovealone),
    weight = 0.5,
    opacity = 1,
    color = "white",
    dashArray = "3",
    fillOpacity = 0.7,
    label = countylabels) %>%
  addLegend("bottomleft", pal = pal_drovealone, values = ~pct_drovealone,
    title = "Drove Alone Mode Share",
    labFormat = labelFormat(suffix = "%"),
    opacity = 1
  )


# display maps
walk_map

(a) Percent of workers who walk to work (static map)

Code

bicycle_map

(b) Percent of workers who bicycle to work (static map)

Code

transit_map

(c) Percent of workers who take public transit to work (static map)

Code

drove_map

(d) Percent of workers who drove alone to work (static map)

Code

# Export maps to png using oceanis package
# export_png(walk_map, chemin = "maps", nomFichier = "walk_map")
# export_png(bicycle_map, chemin = "maps", nomFichier = "bicycle_map")
# export_png(transit_map, chemin = "maps", nomFichier = "transit_map")
# export_png(drove_map, chemin = "maps", nomFichier = "drovealone_map")

Figure 3: Percent of Workers Age 16 and Older by Means of Transportation to Work by County, 2019

Step 5. Design a custom sustainable urban transportation data dashboard

The information provided in this exercise provides a narrow view into commuting trends across the country, which have considerable implications for sustainable urban transportation. Monitoring such trends at different geographic levels can help identify how different areas are performing with regard to, say, active transportation trips (i.e., bicycling and walking) over time.

Step 5 of this exercise asks you to design a custom data dashboard to inform some dimension of sustainable urban transportation (e.g., social equity, environmental performance, economic efficiency). What would you include in such a dashboard (i.e., what indicators or other information)? What stakeholders would potentially benefit from using the dashboard? How could these stakeholders use the dashboard to advance sustainable urban transportation?

Step 6. Submit your completed presentation

Navigate to the course D2L page. Submit/upload your exercise #1 presentation to the appropriate submission folder.